﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DynamicToSql.Samples
{
    class Program
    {
        static void Main(string[] args)
        {
            #region sql server samples

            /* IMPORTANT: RUN script.sql in your own Sql Server instance and update connection string here */
            string connString = @"Data Source=TANITM82\SQLEXPRESS;Initial Catalog=DynamicToSql;Integrated Security=True";
            var connection = new SqlConnection(connString);
            var logger = new ConsoleLogger();

            using (var session = SessionFactory.Create(connection, logger))
            {
                using (var transaction = session.StartTransaction())
                {
                    Console.WriteLine("Example: Delete\n");

                    session.Delete("Person");
                    session.Delete("Address");

                    Console.WriteLine("--------------------------------------------------------------------");

                    Console.WriteLine("Example: Save\n");

                    dynamic p1 = new Entity();
                    p1.Name = "Jamie";
                    p1.Age = 30;
                    p1.CreatedDate = DateTime.Now;
                    session.Save("Person", p1);

                    dynamic p2 = new Entity();
                    p2.Name = "John";
                    p2.Age = 40;
                    p2.CreatedDate = DateTime.Now;
                    session.Save("Person", p2);

                    dynamic p3 = new Entity();
                    p3.Name = "Mary";
                    p3.Age = 50;
                    p3.CreatedDate = DateTime.Now;
                    session.Save("Person", p3);

                    int pId = (int)session.Sql("SELECT @@IDENTITY").ToScalar<decimal>();

                    dynamic a = new Entity();
                    a.PersonId = pId;
                    a.AddressLine = "Main Street #421";
                    a.City = "Fairfax";
                    a.State = "Virginia";
                    a.CreatedDate = DateTime.Now;
                    session.Save("Address", a);

                    Console.WriteLine("--------------------------------------------------------------------");

                    Console.WriteLine("Example: Update\n");

                    dynamic p4 = new Entity();
                    p4.Name = "Maria";
                    p4.UpdatedDate = DateTime.Now;

                    session.Update("Person", p4, new { Name = "Mary" });

                    Console.WriteLine("--------------------------------------------------------------------");

                    Console.WriteLine("Example: Delete\n");

                    session.Delete("Person", new { Name = "Jamie" });

                    Console.WriteLine("--------------------------------------------------------------------");

                    Console.WriteLine("Example: Find\n");

                    foreach (var p in session.Find("Person"))
                        PrintPerson(p);

                    Console.WriteLine();

                    foreach (var p in session.Find("Person", new { Name = "John" }))
                        PrintPerson(p);

                    var c1 = Entity.Property("Name").Function("UPPER({0})") == "JOHN" | Entity.Property("Age") > 45;

                    foreach (var p in session.Find("Person", c1))
                        PrintPerson(p);

                    Console.WriteLine();

                    var c2 = Entity.Property("Name").In("john", "maria") & Entity.Property("Age").Between(40, 45);

                    foreach (var p in session.Find("Person", c2))
                        PrintPerson(p);

                    Console.WriteLine();

                    var c3 = (Entity.Property("Name") == "John" | Entity.Property("Name") == "Maria")
                             & Entity.Property("UpdatedDate") != null;

                    foreach (var p in session.Find("Person", c3))
                        PrintPerson(p);

                    Console.WriteLine("--------------------------------------------------------------------");

                    Console.WriteLine("Example: Count\n");

                    Console.WriteLine(session.Count("Person"));

                    Console.WriteLine();

                    Console.WriteLine(session.Count("Person", new { Name = "John" }));

                    Console.WriteLine();

                    var c4 = Entity.Property("Name").Like("J%") & Entity.Property("UpdatedDate") == null;

                    Console.WriteLine(session.Count("Person", c4));

                    Console.WriteLine("--------------------------------------------------------------------");

                    Console.WriteLine("Example: Property Selectors\n");

                    foreach (var p in session.Find("Person", new PropertySelector().Include("Id").Include("Name")))
                    {
                        Console.WriteLine(p.Id + " " + p.Name);
                    }

                    a.City = "Reston";
                    a.UpdatedDate = DateTime.Now;
                    session.Update("Address", a, new { PersonId = pId }, new PropertySelector().Include("City", "UpdatedDate"));

                    session.Save("Address", a, new PropertySelector().Exclude("UpdatedDate"));

                    foreach (var adr in session.Find("Address"))
                    {
                        PrintAddress(adr);
                    }

                    Console.WriteLine("--------------------------------------------------------------------");

                    Console.WriteLine("Example: Native SQL\n");

                    Console.WriteLine(session.Sql("select count(*) from Person").ToScalar<int>());

                    Console.WriteLine();

                    var list1 = session.Sql("select * from Person").ToList();
                    foreach (var e in list1)
                        PrintPerson(e);

                    Console.WriteLine();

                    var list2 = session.Sql("select * from Person; select * from Address;").ToMultipleList();
                    foreach (var e in list2[0])
                        PrintPerson(e);
                    Console.WriteLine();
                    foreach (var e in list2[1])
                        PrintAddress(e);

                    Console.WriteLine();

                    var list3 = session.Sql("select * from Person where Name=@Name and Age=@Age")
                                       .Parameter("Name", "John")
                                       .Parameter("Age", 40)
                                       .ToList();
                    foreach (var e in list3)
                        PrintPerson(e);

                    Console.WriteLine();

                    var list4 = session.Sql("select P.Name, A.City, A.State from Person as P, Address as A where P.Id = A.PersonId")
                                       .ToList();
                    foreach (var e in list4)
                    {
                        Console.WriteLine(e.Name + " " + e.City + " " + e.State);
                    }

                    Console.WriteLine("--------------------------------------------------------------------");

                    Console.WriteLine("Example: Stored Procedures and Functions\n");

                    var proc = session.Procedure("UpdatePersonAge")
                            .Parameter("Name", "John", DbType.String, ParameterDirection.Input)
                            .Parameter("Age", 41, DbType.Int32, ParameterDirection.Input)
                            .Parameter("RecordsUpdated", null, DbType.Int32, ParameterDirection.Output);

                    proc.Execute();

                    Console.WriteLine(proc["RecordsUpdated"].Value);

                    Console.WriteLine();

                    var list5 = session.Procedure("GetPersons").ToList();
                    foreach (var e in list5)
                    {
                        PrintPerson(e);
                    }

                    Console.WriteLine();

                    var func = session.Function("CountPersons")
                                      .Parameter("Name", "John")
                                      .Parameter("ReturnValue", null, DbType.Int32, ParameterDirection.ReturnValue);
                    func.Execute();

                    Console.WriteLine(func["ReturnValue"].Value);

                    Console.WriteLine("--------------------------------------------------------------------");

                    Console.WriteLine("Example: conversion between dynamic entity and strongly typed entity\n");

                    dynamic entity = new Entity();
                    entity.Id = 1;
                    entity.Name = "mary";
                    entity.Age = 23;
                    entity.CreatedDate = DateTime.Now;
                    entity.UpdatedDate = null;
                    PrintPerson(entity);

                    var person = Entity.ToType<Person>(entity as Entity);
                    PrintPerson(person);

                    entity = Entity.ToEntity<Person>(person);
                    PrintPerson(entity);

                    Console.WriteLine("--------------------------------------------------------------------");

                }

            }

            #endregion

            Console.WriteLine();
            Console.WriteLine("Press any key to continue");
            Console.Read();
        }

        static void PrintPerson(dynamic e)
        {
            Console.WriteLine(e.Id + " " + e.Name + " " + e.Age + " " + e.CreatedDate + " " + e.UpdatedDate);
        }

        static void PrintAddress(dynamic e)
        {
            Console.WriteLine(e.Id + " " + e.PersonId + " " + e.AddressLine + " " + e.City + " " + e.State + " " + e.CreatedDate + " " + e.UpdatedDate);
        }

        static void PrintPerson(Person e)
        {
            Console.WriteLine(e.Id + " " + e.Name + " " + e.Age + " " + e.CreatedDate + " " + e.UpdatedDate);
        }

        static void PrintAddress(Address e)
        {
            Console.WriteLine(e.Id + " " + e.PersonId + " " + e.AddressLine + " " + e.City);
        }

    }

    class Person
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public int Age { get; set; }

        public DateTime CreatedDate { get; set; }

        public DateTime? UpdatedDate { get; set; }
    }

    class Address
    {
        public int Id { get; set; }

        public int PersonId { get; set; }

        public string AddressLine { get; set; }

        public string City { get; set; }

        public string State { get; set; }

        public DateTime CreatedDate { get; set; }

        public DateTime? UpdatedDate { get; set; }
    }
}
